#!/usr/bin/env python
from lib.format_time import *
from lib.HandleMysql import *
from lib.handle_mongo import *
from lib.switch_code import *
from jj_conn import *
from config_tools import *
from lib.load_data import *
from create_table import *
import time
import math
import sys
import re
reload(sys)
sys.setdefaultencoding('utf-8')


def get_product_info(date):
    conn = set_prod_conn()
    ib_conn = set_ib_conn()
    stat_table = 't_product'
    line_list = []
    
    query = 'select id,product_line_id,code,name from t_product_unit'
    items = conn.get_results(query)
    for item in items:
        prod_id = item[0]
        line_id = item[1]
        prod_code = item[2]
        prod_name = item[3]
        
        line_code = ''
        line_name = ''
        type_id = 0
        type_code = ''
        type_name = ''
        q = "select type_id,code,name from t_product_line where id=%s"%line_id
        lines = conn.get_results(q)
        if len(lines) > 0:
            type_id = lines[0][0]
            line_code = lines[0][1]
            line_name = lines[0][2]
        
        if type_id>0:
            q = "select code,name from t_type where id=%s"%type_id
            types = conn.get_results(q)
            if len(types) > 0:
                type_code = types[0][0]
                type_name = types[0][1]
                
        
        param = [prod_id,prod_name,prod_code,line_id,line_name,line_code,type_id,type_name,type_code]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
        
    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/jingjia/product/"
    file_name = "product_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)    
    load_data(ib_conn,path+file_name,stat_table,'\t')
    
    conn.close()
    ib_conn.close()
    

def get_jingjia_config(date):
    conn = set_jingjia_conn()
    ib_conn = set_ib_conn()
    dict_conn = set_dict_conn()
    stat_table = 't_jingjia_config'
    line_list = []
    
    query = 'select configid,productid,localid,dispcateid,jingjiatype,maxnum,maxagencybalance,minstartprice,maxstartprice,minaddprice,maxaddprice,configtype,effid,failid,createtime,updatetime,state from t_jingjiaconfig'
    items = conn.get_results(query)
    print "get_jingjia_config get t_jingjiaconfig lens: %s \n" %len(items)
    for item in items:
        config_id = item[0]
        prod_id = item[1]
        city = item[2]
        cate_id = item[3]
        jj_type = item[4]
        max_num = item[5]
        max_agency_balance = item[6]
        min_start_price = item[7]
        max_start_price = item[8]
        min_add_price = item[9]
        max_add_price = item[10]
        config_type = item[11]
        eff_id = item[12]
        fail_id = item[13]
        create_time = item[14]
        update_time = item[15]
        state = item[16]
        
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        
        dc = get_local_citys(city,dict_conn)
        if dc != None:
            if len(dc) >0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc)>1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc)>2:
                local_id= dc[2][0]
                local_name = dc[2][1]
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        dc = get_cates(cate_id,dict_conn)
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
        
        param = [config_id,prod_id,jj_type,max_num,max_agency_balance,min_start_price,max_start_price,min_add_price,max_add_price,config_type,eff_id,fail_id,create_time,update_time,state,city_id,city_name,area_id,area_name,local_id,local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
        
    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/jingjia/config/"
    file_name = "jingjia_config_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)    
    load_data(ib_conn,path+file_name,stat_table,'\t')
    
    conn.close()
    ib_conn.close()
    dict_conn.close()

def get_jingjia_data(date):
    conn = set_jingjia_conn()
    ib_conn = set_ib_conn()
    dict_conn = set_dict_conn()
    year_str = get_date_info(date,'%Y')
    stat_table = 't_jingjia%s'%year_str
    line_list = []
    
    try:
        #pass
        create_jingjia_info(ib_conn,stat_table)
    except Exception,e:
        print 'create table error:',e
        
    #query = "select ifnull(max(jingjia_id),0) from t_jingjia"
    #items = ib_conn.get_results(query)
    #max_id = items[0][0]
    
    
    #query = "select jingjiaid,infoid,productid,localid,dispcateid,userid,starttime,endtime,bidtype,price,agencybalance,effid,failid,createtime,updatetime,state,costscore,paytype,costmoney,handleid from t_jingjia where jingjiaid>%s"%max_id
    
    query = "select jingjiaid,infoid,productid,localid,dispcateid,userid,starttime,endtime,bidtype,price,agencybalance,effid,failid,createtime,updatetime,state,costscore,paytype,costmoney,handleid from t_jingjia where date(createtime)='%s'"%date #endtiume t_jingjiahistory
    items = conn.get_results(query)
    print "get_jingjia_data get t_jingjia lens: %s \n" % len(items) 
    for item in items:
        jingjia_id = item[0]
        info_id = item[1]
        product_id = item[2]
        city = item[3]
        cate_id = item[4]
        user_id = item[5]
        start_time = item[6]
        end_time = item[7]
        bid_type = item[8]
        price = item[9]
        agency_balance = item[10]
        eff_id = item[11]
        fail_id = item[12]
        create_time = item[13]
        update_time = item[14]
        state = item[15]
        cost_score = item[16]
        pay_type = item[17]
        cost_money = item[18]
        handle_id = item[19]
        
        
        city_id = 0
        city_name = ''
        area_id = 0
        area_name = ''
        local_id= 0
        local_name = ''
        
        dc = get_local_citys(city,dict_conn)
        if dc != None:
            if len(dc) >0:
                city_id = dc[0][0]
                city_name = dc[0][1]
            if len(dc)>1:
                area_id = dc[1][0]
                area_name = dc[1][1]
            if len(dc)>2:
                local_id= dc[2][0]
                local_name = dc[2][1]
        cat1 = 0
        cat1name = ''
        cat2 = 0
        cat2name=''
        cat3 = 0
        cat3name=''
        dc = get_cates(cate_id,dict_conn)
        if dc != None:
            if len(dc) > 0:
                cat1 = dc[0][0]
                cat1name = dc[0][1]
            if len(dc) > 1:
                cat2 = dc[1][0]
                cat2name=dc[1][1]
            if len(dc) > 2:
                cat3 = dc[2][0]
                cat3name=dc[2][1]
                
        cash_amount = 0
        q = "select cash_amount from t_order_info where jingjia_id=%s"%jingjia_id
        its = ib_conn.get_results(q)
        if len(its)>0:
            cash_amount = its[0][0]
            
        param = [jingjia_id,user_id,product_id,info_id,start_time,end_time,bid_type,price,agency_balance,eff_id,fail_id,create_time,update_time,state,cost_score,pay_type,cost_money,handle_id,city_id,city_name,area_id,area_name,local_id,local_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,cash_amount]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
        
    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/jingjia/config/"
    file_name = "jingjia_info_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)    
    load_data(ib_conn,path+file_name,stat_table,'\t')
    
    conn.close()
    ib_conn.close()
    dict_conn.close()
 
 
def get_info_data_stat(date):
    conn = set_ib_conn()
    stat58_conn = set_stats_conn()
    ib_conn = set_pv_conn()
    ad_conn = set_ads_conn()
    stat_table = "t_info_data_stats"
    line_list = []
    
    year_str = get_date_info(date,'%Y')
    jj_table = 't_jingjia%s'%year_str
    
    query = "select city_id,city_name,area_id,area_name,disp_cate1_id,disp_cate1_name,disp_cate2_id,disp_cate2_name,ifnull(sum(case when State=3 then cost_money-agency_balance end),0),sum(cash_amount),count(case when State=3 then 1 end),count(1),count(distinct case when State=3 then user_id end),count(distinct user_id)  from %s where date(start_time)='%s' group by city_id,area_id,disp_cate1_id,disp_cate2_id "%(jj_table,date)
    items = conn.get_results(query)
    for item in items:
        city_id = item[0]
        city_name = item[1]
        area_id = item[2]
        area_name = item[3]
        if area_name == None:
            area_name = ''
        cat1 = item[4]
        cat1name = item[5]
        cat2 = item[6]
        cat2name = item[7]
        cat3 = 0
        cat3name = ''
        
        #print 'test1'
        pay_amount = item[8]
        cash_amount = item[9]
        success_count = item[10]
        info_count = item[11]
        success_user_count = item[12]
        user_count = item[13]
        
        jj_pv = 0
        clicks = 0
        vip_user_amount = 0
        vip_user_cash_amount = 0
        vip_user_count = 0
        vip_user_amount_rate = 0.0
        vip_user_rate = 0.0
        q = "select sum(pv),sum(clicks),sum(case when is_vip=1 then pay_amount end),count(case when is_vip=1 then 1 end),sum(case when is_vip=1 then cash_amount end) from t_info_detail_stats where city_id=%s and area_id=%s and cate1_id=%s and cate2_id=%s and stat_date='%s'"%(city_id,area_id,cat1,cat2,date)
        #print   q
        its = conn.get_results(q)
        if len(its)>0:
            jj_pv = its[0][0]
            clicks = its[0][1]
            vip_user_amount = its[0][2]
            vip_user_count = its[0][3]
            vip_user_cash_amount = its[0][4]
        if jj_pv == None:
            jj_pv = 0
        #print 'test2'    
        if vip_user_amount == None:
            vip_user_amount =0
        
        if pay_amount != 0:
            vip_user_amount_rate = float(vip_user_amount)/float(pay_amount)
        if user_count != 0:
            vip_user_rate = float(vip_user_count)/float(user_count)
        
        show_pv = 0.0
        if success_count > 0:
            show_pv = float(jj_pv)/float(success_count)

        #print 'test3'
        area = city_id
        if area_id>0:
            area = area_id
            
        pv = 0
        total_pv = 0
        tb = "fc_list_analytics_%s"%get_date_info(date,'%Y%m')
        q = "select sum(actionPv) from %s where area=%s  and cate=%s and stat_date='%s'"%(tb,area,cat2,date)
        #print q
        its = stat58_conn.get_results(q)
        if len(its)>0:
            pv = its[0][0]
        #print 'test4'    
        tb = "fc_show_info_%s"%get_date_info(date,'%Y%m')
        q = "select sum(pv) from %s where local_id=%s  and cate_id=%s  and stat_date='%s'"%(tb,area,cat2,date)
        its = ib_conn.get_results(q)
        if len(its)>0:
            total_pv = its[0][0]
        
        if pv == None:
            pv = 0
        if total_pv == None:
            total_pv = 0
        if clicks== None:
            clicks = 0
            
        channel_cpm = 0.0
        jj_cpm = 0.0
        channel_ctr = 0.0
        jj_ctr = 0.0
        cpc = 0.0
        arpu = 0.0
        if pv != 0 :
            channel_cpm = 1000*float(pay_amount)/float(pv)
            channel_ctr = float(clicks)/float(pv)
        if jj_pv != 0 :    
            jj_cpm = 1000*float(pay_amount)/float(jj_pv)
            jj_ctr = float(clicks)/float(jj_pv)
        if clicks != 0:
            cpc = float(pay_amount)/float(clicks)
        if success_user_count != 0:
            arpu = float(pay_amount)/float(success_user_count)
        
        page_ad_count = 0
        
        param = [date,city_id,city_name,area_id,area_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,pay_amount,cash_amount,success_count,success_user_count,info_count,user_count,vip_user_count,vip_user_amount,vip_user_amount_rate,vip_user_rate,pv,jj_pv,show_pv,total_pv,clicks,channel_cpm,jj_cpm,channel_ctr,jj_ctr,cpc,arpu,page_ad_count,vip_user_cash_amount]
        line_list.append('\t'.join(map(lambda i : str(i), param)))

    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/jingjia/data/"
    file_name = "t_info_data_stats_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)    
    load_data(conn,path+file_name,stat_table,'\t')
    
    conn.close()
    ib_conn.close()
    ad_conn.close()
    stat58_conn.close()

def get_info_detail_stat(date):
    conn = set_ib_conn()
    stat58_conn = set_stats_conn()
    ib_conn = set_pv_conn()
    ad_conn = set_ads_conn()
    dict_conn = set_dict_conn()
    stat_table = "t_info_detail_stats"
    line_list = []

    year_str = get_date_info(date,'%Y')
    jj_table = 't_jingjia%s'%year_str
    query = "select user_id,city_id,city_name,area_id,area_name,disp_cate1_id,disp_cate1_name,disp_cate2_id,disp_cate2_name,ifnull(sum(case when State=3 then cost_money-agency_balance end),0),count(1),count(case when State=3 then 1 end),min(price),max(price),avg(price),sum(cash_amount) from %s where date(start_time)='%s' group by user_id,city_id,area_id,disp_cate1_id,disp_cate2_id"%(jj_table,date)
    #print query
    items = conn.get_results(query)
    for item in items:
        user_id = item[0]
        city_id = item[1]
        city_name = item[2]
        area_id = item[3]
        area_name = item[4]
        if area_name == None:
            area_name = ''
        cat1 = item[5]
        cat1name = item[6]
        cat2 = item[7]
        cat2name = item[8]
        cat3 = 0
        cat3name = ''
        
        pay_amount = item[9]
        info_count = item[10]
        success_count = item[11]
        
        min_price = item[12]
        max_price = item[13]
        avg_price = item[14]
        cash_amount = item[15]
        #print cash_amount
        area = city_id
        if area_id>0:
            area = area_id
        
        area = area_name
        if area_id > 0:
            q = "select localname from t_displocal where localid=%s limit 1"%area_id
            its = dict_conn.get_results(q)
            if len(its)>0:
                area = its[0][0]
        
        pv = 0
        clicks = 0
        tb = "fc_post_user_show_valid_%s"%get_date_info(date,'%Y%m')
        q = "select ifnull(sum(show_count),0) from %s where post_type in ('6','f') and city1='%s' and ifnull(city2,'')='%s' and cate2='%s' and user_id=%s and stat_date='%s'"%(tb,city_name,area,cat2name,user_id,date)
        its = ib_conn.get_results(q)
        if len(its)>0:
            pv = its[0][0]
            
        tb = "fc_click_info_%s"%get_date_info(date,'%Y%m')
        q = "select ifnull(sum(click_count),0) from %s where product_type='fangchanjingjia' and city1='%s' and ifnull(city2,'')='%s' and cate2='%s' and user_id=%s and stat_date='%s'"%(tb,city_name,area,cat2name,user_id,date)
        #print q
        its = ib_conn.get_results(q)
        if len(its)>0:
            clicks = its[0][0]
        
        cpm = 0.0
        ctr = 0.0
        cpc = 0.0
        if pv != 0 :
            cpm = 1000*float(pay_amount)/float(pv)
            ctr = float(clicks)/float(pv)
        if clicks != 0:
            cpc = float(pay_amount)/float(clicks)
        
        cash_account = 0
        is_vip = 0
        user_name = ''
        tb = "user_account_%s"%get_date_info(date,'%Y%m')
        q = "select cash_account,is_vip,user_name from %s where user_id=%s and stat_date='%s'"%(tb,user_id,date)
        its = ad_conn.get_results(q)
        if len(its)>0:
            cash_account = its[0][0]
            is_vip = its[0][1]
            user_name = its[0][2]
        sale_man_id = 0
        sale_man_name = ''
        sale_man_department = ''
        contact_person = ''
        contact_tel = ''
        q = "select sale_man_id,sale_man_name,sale_man_department,contact_person,contact_tel from order_info where user_id=%s order by order_id desc limit 1"%user_id
        its = stat58_conn.get_results(q)
        if len(its)>0:
            sale_man_id  = its[0][0]
            sale_man_name  = its[0][1]
            sale_man_department  = its[0][2]
            contact_person  = its[0][3]
            contact_tel  = its[0][4]
        
        param =[date,user_id,user_name,city_id,city_name,area_id,area_name,cat1,cat1name,cat2,cat2name,cat3,cat3name,pay_amount,cash_amount,info_count,success_count,pv,clicks,cpm,ctr,cpc,cash_account,max_price,min_price,avg_price,is_vip,sale_man_id,sale_man_name,sale_man_department,contact_person,contact_tel]
        line_list.append('\t'.join(map(lambda i : str(i), param)))
        #print param
        #break
    cont = '\n'.join(line_list)
    cont = cont + '\n'
    path = "/work3/opt/analytic/lh_tmp_data/jingjia/detail/"
    file_name = "t_info_detail_stats_%s.txt"%date.replace('-',"")
    writeTextToFile(path,file_name,"w",cont)    
    load_data(conn,path+file_name,stat_table,'\t')
    
    conn.close()
    ib_conn.close()
    ad_conn.close()
    stat58_conn.close()
    
def execute(date):
    #get_jingjia_data(date)
    try:
        pass
        #get_product_info(date)
    except Exception,e:
        print 'get_product_info:',e
    try:
        pass
        get_jingjia_data(date)
    except Exception,e:
        print 'get_jingjia_data:',e

    try:
        pass
        get_info_detail_stat(date)
    except Exception,e:
        print 'get_info_detail_stat:',e
    
    try:
        pass
        get_info_data_stat(date)
    except Exception,e:
        print 'get_info_data_stat:',e
def main():

   #date = '2012-05-28'
   #execute(date)   
   if len(sys.argv)==1:
        start_date=get_yesterday()
        end_date=start_date
   elif len(sys.argv)==2:
        start_date=format_date(sys.argv[1])
        end_date=start_date
   elif len(sys.argv)==3:
        start_date=format_date(sys.argv[1])
        end_date=format_date(sys.argv[2])
   stat_date=start_date
   
   #ib_conn = set_ib_conn()
   #stat_table = 't_jingjia2013'
    
   while stat_date>=start_date and stat_date<=end_date:
        print "The program of the date of %s is beginning at %s " %(stat_date,get_now())
        #
        #get_jingjia_data(stat_date)
        execute( stat_date)
        
        
        
        #path = "/work3/opt/analytic/lh_tmp_data/jingjia/config/"
        #file_name = "jingjia_info_%s.txt"%stat_date.replace('-',"")
        #load_data(ib_conn,path+file_name,stat_table,'\t')
        
        print "The program of the date of %s is end at %s " %(stat_date,get_now())
        stat_date=var_date(stat_date)

   #ib_conn.close()
   
if __name__=='__main__':
    main()
    
    
